Integration Services (OData)

PAGE CONTENTS

'Integration Services' are services provided by IPS Server for a Planning Space tenant which allow client users (either human or machine) to make requests for data held in the tenant through API requests; the requests use the OData standard protocol, hence the terms 'OData API interface' and 'Integration Services' are mentioned somewhat interchangably for Planning Space.

The processing of OData API requests by an IPS Server is controlled by a number of settings which are described in this topic. For IPS Server configuration that applies to all of the Planning Space APIs, see the topic API configuration.

For information about using Integration Services and OData, see the Planning Space Web API Guide.

The IPS Server settings mentioned here can be set in the IPS Manager web interface, or using the IPS Admin API (see the IPS Admin API Guide), or using the IPS PowerShell module (Automation cmdlets). To access any of these you will need to have the user credentials for an authorized IPS Administrator.

Windows authentication for OData

An additional authentication mode for OData API requests based on Windows authentication is available. It is disabled by default and can be enabled using the IPS service setting 'Enable Windows Authentication' (see Service configuration). The setting applies to all Planning Space tenants and it is not possible to enable/disable at the tenant level.

Application service settings

These settings apply at the application level (i.e., Dataflow, Economics, Financials).

Note that these settings apply to all tenants in the IPS Server; it isn't possible to have different settings for some tenants.

All are set, separately for each application, in the IPS Manager 'Applications' screen under the 'Advanced' tab, in the 'Service Settings' box. For example, this screenshot shows the settings for Dataflow:

IntegrationServicesIsServiceEnabled

If set to True, Integration Services are enabled for the application. This is the default setting.

Note that the Integration Services are provided by the Web API resources named '/data/v1/...'. The other resources in the the Web APIs, named '/api/v1/...' are not controlled by this setting, and are always enabled so long as the application is available (see Application availability).

IntegrationServicesRemoteErrorsEnabled

Set this to True to provide detailed error information during troubleshooting/testing of the OData API. A full internal stack trace of the error is returned, whereas only an HTTP 500 status message will be returned if the setting is False.

This setting should be set to False during normal operation of the OData API. Note: the default setting is True for the Economics and Financials APIs.

IntegrationServicesMaxDocumentPageSize (only for Dataflow)

OData API requests can involve potentially very large amounts of data (possibly in the gigabytes range), depending on the data content of a Planning Space tenant. API paging (also known as pagination) is used to put limits on the amount of data which the IPS server sends in one API response: when the amount of data exceeds a specified threshold (the 'page size'), then the initial API response contains just one 'page' of data, and a URI link is included in the response to specify how to get the next page of data. In this way, the client user can decide if and when to submit further API requests to get the further 'pages' of data that are available in the server.

This page size setting specifies the maximum number of documents per page when returning data from the Dataflow 'Documents' endpoint ('PlanningSpaceDataflow/Data/V1/Documents'). The default value is 2000 and the recommended range is 10 to 5000.

This setting does not affect the page size for OData API requests involving the denormalized entity sets (AllVariablesTall, AllScalarVariablesWide, and AllPeriodicVariablesWide).

IntegrationServicesMaxVariablePageSize (only for Economics and Financials)

This page size setting determines the maximum number of variables per page when requesting result set data using the endpoints '/Data/V1/ResultSets(id)/ResultSetRuns(runId)/Variables' and '/Data/V1/ResultSets(id)/ResultSetRuns(runId)/RunProjects(projectId)/Variables'.

The default value is 50 and the allowed range is 10 to 10000.

This setting does not affect the page size for OData API requests involving the denormalized entity sets (AllVariablesTall, AllScalarVariablesWide, and AllPeriodicVariablesWide).

ODataWideAndTallTableMaxRowsPerRequest

This setting determines the maximum number of rows that will be returned when requesting 'wide' and 'tall' tables in OData API requests involving the denormalized entity sets (AllVariablesTall, AllScalarVariablesWide and AllPeriodicVariablesWide). It is not the same as the (maximum) page size for OData requests involving denormalized data, which is determined by an algorithm that depends on several factors and is explained below.

Using the 'pagesize' API parameter will override this setting.

The default value is 10000 and the recommended range is 1000 to 200000. Finding an optimal value for this setting in a specific production environment depends on the typical characteristics of the OData API requests (number of variables, runs, periodicity, number of periods, etc.), and also depends on the overall server machine(s)'s resources (processors, memory) and the typical loading of the server machine(s) by all types of jobs/processes. To get better API performance (that is, returning larger pages of data) requires more resource usage (more memory and CPU will be occupied), thus it involves finding an acceptable trade-off with the competing demands on the IPS Server (from batch jobs, etc.).

Getting and setting application settings using the IPS PowerShell module

You can use the IPS PowerShell module to get the application settings using the command 'Get-ApplicationServiceSetting', for example:

Get-ApplicationServiceSetting -Application PlanningSpaceDataflow -Category IntegrationServices -Name IntegrationServicesMaxDocumentPageSize

with return value '2000' for the default setting. And:

Get-ApplicationServiceSetting -Application PlanningSpaceFinancials -Category IntegrationServices -Name IntegrationServicesIsServiceEnabled

with return value 'True' for the default setting.

You can change a setting with the 'Set-ApplicationServiceSetting' command, for example:

Set-ApplicationServiceSetting -Application PlanningSpaceDataflow -Category IntegrationServices -Name IntegrationServicesMaxDocumentPageSize -Value 5000

Page size algorithm for denormalized entity sets (Tall and Wide tables) in Dataflow

The following algorithm is used to calculate the page size setting PageSize for the API requests:

• '/PlanningSpaceDataflow/data/v1/AllVariablesTall'
• '/PlanningSpaceDataflow/data/v1/AllScalarVariablesWide'
• '/PlanningSpaceDataflow/data/v1/AllPeriodicVariablesWide'

Note: the value of PageSize cannot exceed the cap value of 1000.

PageSize = ODataWideAndTallTableMaxRowsPerRequest / EstimatedRowCountPerDocument

where:

EstimatedRowCountPerDocument = EstimatedRowsGivenSettings * ScenarioTagsRequested * RevisionTagsRequested

and EstimatedRowsGivenSettings depends on the entity set, as shown in the table:

EstimatedRowsGivenSettings =  CurrencyDecksRequestedCount * CurrenciesRequestedCount * RealNominalsRequestedCount * 
  UnitSystemsRequestedCount * ScaleSizesRequestedCount * InflationDatesRequestedCount * 
  PeriodicitiesRequested.Each(EstimateRowsGivenPeriodicity)

EstimatedRowsGivenSettings =  CurrencyDecksRequestedCount * CurrenciesRequestedCount * RealNominalsRequestedCount * 
  UnitSystemsRequestedCount * ScaleSizesRequestedCount * InflationDatesRequestedCount

EstimatedRowsGivenSettings =  CurrencyDecksRequestedCount * CurrenciesRequestedCount * RealNominalsRequestedCount * 
  UnitSystemsRequestedCount * ScaleSizesRequestedCount * InflationDatesRequestedCount * 
  VariablesRequested.Each(EstimateRowsGivenVariable)

The value of PageSize for wide tables is finally adjusted by a factor based on the number of variables that have been requested:

PageSize = PageSize / Max(1, (VariableCount / 50))